package org.skipper.samples.excel;

import com.alibaba.excel.util.DateUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.fasterxml.jackson.annotation.JsonFormat;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import sun.net.www.http.HttpClient;

import javax.net.ssl.*;
import java.io.*;
import java.lang.reflect.Field;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.security.SecureRandom;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

/**
 * Created with IntelliJ IDEA.
 * Description:
 *
 * @author shoujian
 * Date: 2022-10-17
 * Time: 11:26
 */
public class PoiUtil {

    public static final String EXCEL_XLS = ".xls";
    public static final String EXCEL_XLSX = ".xlsx";

    /**
     * 写入本地文件
     *
     * @param data 数据集
     * @param path 文件路径
     */
    public void write(List<?> data, String path) {
        if (data.size() == 0) {
            return;
        }
        write(data, data.getClass(), path);
    }

    /**
     * 写入本地文件
     *
     * @param data  数据集
     * @param clazz 列名基于实体注解
     * @param path  文件路径
     */
    public static <T> void write(List<?> data, Class<T> clazz, String path) {
        createFile(path);
        File file = new File(path);
        Workbook workbook = writeWorkbook(data, clazz, file.getName());
        try (FileOutputStream outputStream = new FileOutputStream(file)) {
            assert workbook != null;
            workbook.write(outputStream);
        } catch (Exception e) {
//            LOGGER.error("写入Excel文件失败", e);
        }
    }

    /**
     * 多种数据写入同一个workbook
     */
    public static Workbook writeWorkbook(List<List<?>> data, String fileName, Boolean isMultiSheet) {
        int total = data.stream().mapToInt(List::size).sum();
        Workbook workbook = buildWorkbook(fileName, total);
        Integer startRowNum = 0;
        if (isMultiSheet) {
            for (int i = 0; i < data.size(); i++) {
                startRowNum = 0;
                List<?> list = data.get(i);
                if (list.size() == 0) {
                    continue;
                }
                Class<?> clazz = list.get(0).getClass();
                // 判断是否设置“工作铺名称”
                String sheetName = "sheet" + (i + 1);
                if (clazz.isAnnotationPresent(ExcelSheet.class)) {
                    sheetName = clazz.getAnnotation(ExcelSheet.class).value();
                }
                Sheet sheet = workbook.createSheet(sheetName);
                writeSheet(sheet, workbook, list, clazz, startRowNum);
            }
        } else {
            // 判断是否设置“工作铺名称”
            String sheetName = "sheet";
            Sheet sheet = workbook.createSheet(sheetName);
            for (List<?> list : data) {
                if (list.size() == 0) {
                    continue;
                }
                Class<?> clazz = list.get(0).getClass();
                writeSheet(sheet, workbook, list, clazz, startRowNum);
                startRowNum += 4;
            }
        }

        return workbook;
    }

    /**
     * 写入Workbook
     *
     * @param data 数据集合
     */
    public static Workbook writeWorkbook(List<?> data, List<String> fieldNames, String fileName) {
        if (data.size() == 0 || fieldNames.size() == 0) {
            return null;
        }

        Workbook workbook = buildWorkbook(fileName, data.size());

        Class<?> aClass = data.get(0).getClass();
        // 判断是否设置“工作铺名称”
        String sheetName = "sheet";
        if (aClass.isAnnotationPresent(ExcelSheet.class)) {
            sheetName = aClass.getAnnotation(ExcelSheet.class).value();
        }
        Sheet sheet = workbook.createSheet(sheetName);

        // 设置表头（列名）
        Row row = sheet.createRow(0);

        // 设置单元格样式
        CellStyle headerStyle = buildHeaderStyle(workbook);

        // 列索引
        int columnIndex = 0;
        // 列名称
        String columnName;
        ExcelField excelField;
        for (String fieldName : fieldNames) {
            Field field = existsField(aClass, fieldName);
            if (field != null) {
                // 设置属性可访问
                field.setAccessible(true);
                if (field.isAnnotationPresent(ExcelField.class)) {
                    // 获取注解
                    excelField = field.getAnnotation(ExcelField.class);
                    columnName = excelField.columnName();
                    // 创建单元格
                    createCell(row, columnIndex, columnName, headerStyle);
                    columnIndex++;
                }
            }
        }

        // 行索引  因为表头已经设置，索引行索引从1开始
        int rowIndex = 1;
        for (Object obj : data) {
            // 创建新行，索引加1,为创建下一行做准备
            row = sheet.createRow(rowIndex++);
            columnIndex = 0;
            for (String fieldName : fieldNames) {
                Field field = existsField(aClass, fieldName);
                if (field != null) {
                    // 设置属性可访问
                    field.setAccessible(true);
                    if (field.isAnnotationPresent(ExcelField.class)) {
                        try {
                            Object value = typeConvert(field.get(obj), field);
                            // 创建单元格  field.get(obj)从obj对象中获取值设置到单元格中
                            createCell(row, columnIndex, value, null);
                        } catch (IllegalAccessException e) {
//                            LOGGER.error("对象非法访问异常", e);
                        }
                        columnIndex++;
                    }
                }
            }
        }

        return workbook;
    }

    /**
     * 写入Workbook
     *
     * @param data 数据集合
     */
    private static <T> Workbook writeWorkbook(List<?> data, Class<T> clazz, String fileName) {
        if (data.size() == 0) {
            return null;
        }

        Workbook workbook = buildWorkbook(fileName, data.size());

        // 判断是否设置“工作铺名称”
        String sheetName = "sheet";
        if (clazz.isAnnotationPresent(ExcelSheet.class)) {
            sheetName = clazz.getAnnotation(ExcelSheet.class).value();
        }
        Sheet sheet = workbook.createSheet(sheetName);

        // 设置表头（列名）
        Row row = sheet.createRow(0);

        CellStyle headerStyle = buildHeaderStyle(workbook);

        // 列索引
        int columnIndex = 0;
        // 列名称
        String columnName;
        ExcelField excelField;
        Field[] declaredFields = clazz.getDeclaredFields();
        for (Field field : declaredFields) {
            if (field != null) {
                // 设置属性可访问
                field.setAccessible(true);
                if (field.isAnnotationPresent(ExcelField.class)) {
                    // 获取注解
                    excelField = field.getAnnotation(ExcelField.class);
                    columnName = excelField.columnName();
                    // 创建单元格
                    createCell(row, columnIndex, columnName, headerStyle);
                    columnIndex++;
                }
            }
        }

        // 行索引  因为表头已经设置，索引行索引从1开始
        int rowIndex = 1;
        JsonFormat jsonFormat;
        for (Object obj : data) {
            // 创建新行，索引加1,为创建下一行做准备
            row = sheet.createRow(rowIndex++);
            columnIndex = 0;
            for (Field field : declaredFields) {
                if (field != null) {
                    // 设置属性可访问
                    field.setAccessible(true);
                    if (field.isAnnotationPresent(ExcelField.class)) {
                        try {
                            Object value = typeConvert(field.get(obj), field);
                            // 创建单元格  field.get(obj)从obj对象中获取值设置到单元格中
                            createCell(row, columnIndex, value, null);
                        } catch (IllegalAccessException e) {
//                            LOGGER.error("对象非法访问异常", e);
                        }
                        columnIndex++;
                    }
                }
            }
        }

        return workbook;
    }

    /**
     * 写入workbook，可重复传入使用
     *
     * @param data 数据集合
     */
    private static <T> Workbook writeWorkbook(List<?> data, Class<T> clazz, List<String> fieldNames, String fileName, Workbook workbook) {
        Sheet sheet;
        Row row;
        // 行索引
        int rowIndex = 1;
        // 列索引
        int columnIndex = 0;

        if (workbook == null) {
            workbook = buildWorkbook(fileName, data.size());
            // 判断是否设置“工作铺名称”
            String sheetName = "sheet";
            if (clazz.isAnnotationPresent(ExcelSheet.class)) {
                sheetName = clazz.getAnnotation(ExcelSheet.class).value();
            }
            sheet = workbook.createSheet(sheetName);

            // 设置表头（列名）
            row = sheet.createRow(0);

            // 设置样式
            CellStyle headerStyle = buildHeaderStyle(workbook);

            // 列名称
            String columnName;
            ExcelField excelField;
            for (String fieldName : fieldNames) {
                Field field = existsField(clazz, fieldName);
                if (field != null) {
                    // 设置属性可访问
                    field.setAccessible(true);
                    if (field.isAnnotationPresent(ExcelField.class)) {
                        // 获取注解
                        excelField = field.getAnnotation(ExcelField.class);
                        columnName = excelField.columnName();
                        // 创建单元格
                        createCell(row, columnIndex, columnName, headerStyle);
                        columnIndex++;
                    }
                }
            }
        } else {
            sheet = workbook.getSheetAt(0);
            rowIndex = sheet.getLastRowNum() + 1;
        }

        for (Object obj : data) {
            // 创建新行，索引加1,为创建下一行做准备
            row = sheet.createRow(rowIndex++);
            columnIndex = 0;
            for (String fieldName : fieldNames) {
                Field field = existsField(clazz, fieldName);
                if (field != null) {
                    // 设置属性可访问
                    field.setAccessible(true);
                    if (field.isAnnotationPresent(ExcelField.class)) {
                        try {
                            // 创建单元格  field.get(obj)从obj对象中获取值设置到单元格中
                            createCell(row, columnIndex, field.get(obj), null);
                        } catch (IllegalAccessException e) {
//                            LOGGER.error("对象非法访问异常", e);
                        }
                        columnIndex++;
                    }
                }
            }
        }

        return workbook;
    }


    /**
     * 读取Excel文件内容
     *
     * @param path  读取Excel文件的路径
     * @param clazz 返回的实体类泛型
     * @param <T>   泛型
     * @return 文件内容
     */
    public static <T> List<T> read(String path, Class<T> clazz) {
        return read(path, clazz, 0);
    }

    /**
     * 读取Excel文件内容
     *
     * @param path     读取Excel文件的路径
     * @param clazz    返回的实体类泛型
     * @param sheetNum sheet下标
     * @param <T>      泛型
     * @return 文件内容
     */
    public static <T> List<T> read(String path, Class<T> clazz, int sheetNum) {
        File file = new File(path);
        try (FileInputStream inputStream = new FileInputStream(file)) {
            return read(inputStream, file.getName(), clazz, sheetNum);
        } catch (Exception e) {
//            LOGGER.error("读取Excel文件失败", e);
            return Collections.emptyList();
        }
    }

    public static void main(String[] args) {
        List<Map<String, Object>> dataList = PoiUtil.read("/Users/shoujian/Documents/00work/临时文件/database(1)/01_433.xlsx");
    }

//
//    public static void main(String[] args) {
//        List<Map<String, Object>> dataList = PoiUtil.read("/Users/shoujian/Documents/00work/临时文件/query-impala-36403.xlsx");
//
//        String tagStr = "[{\"tagId\":\"etqN3RDAAAha8QPuF41we5Qz4zyaU-Sg\",\"cityName\":\"北京市\",\"cityId\":1},{\"tagId\":\"etqN3RDAAAvW9UMvP0DIhQXfuE6Vxv2Q\",\"cityName\":\"上海市\",\"cityId\":2},{\"tagId\":\"etqN3RDAAAEWKVlA_AtmrTtqunn1pBbw\",\"cityName\":\"重庆市\",\"cityId\":3},{\"tagId\":\"etqN3RDAAAQJZU6BvwdAsuTpdbiKQcEQ\",\"cityName\":\"安徽省\",\"cityId\":4},{\"tagId\":\"etqN3RDAAApKB68wDOZPdp_ThsmTnfPA\",\"cityName\":\"福建省\",\"cityId\":5},{\"tagId\":\"etqN3RDAAAfEbULANon7fJuTMCd8v4Ag\",\"cityName\":\"甘肃省\",\"cityId\":6},{\"tagId\":\"etqN3RDAAAq5oJKPmcsA4Y4fMpDQiFdQ\",\"cityName\":\"广东省\",\"cityId\":7},{\"tagId\":\"etqN3RDAAA0g4syROy_orR9VEpUEytmg\",\"cityName\":\"广西壮族自治区\",\"cityId\":8},{\"tagId\":\"etqN3RDAAA-RAyT-02eRieQ0N39RH1Kg\",\"cityName\":\"贵州省\",\"cityId\":9},{\"tagId\":\"etqN3RDAAACawpJXfO8GKRBXRGcvzY_g\",\"cityName\":\"海南省\",\"cityId\":10},{\"tagId\":\"etqN3RDAAADu-C7AXN16aCJjkE8phB4w\",\"cityName\":\"河北省\",\"cityId\":11},{\"tagId\":\"etqN3RDAAAIgssPpJOXiYz8wxVjPqRsQ\",\"cityName\":\"黑龙江省\",\"cityId\":12},{\"tagId\":\"etqN3RDAAAB2ZI4g_jTmR3JGPUAeCdKA\",\"cityName\":\"河南省\",\"cityId\":13},{\"tagId\":\"etqN3RDAAAhZ9JjIsPSavNEOZlDaq71Q\",\"cityName\":\"香港特别行政区\",\"cityId\":14},{\"tagId\":\"etqN3RDAAAVCXtc9Fv6PXqDuAmbEVBIQ\",\"cityName\":\"湖北省\",\"cityId\":15},{\"tagId\":\"etqN3RDAAADLSZrZPKNATSTOKxoWQUIg\",\"cityName\":\"湖南省\",\"cityId\":16},{\"tagId\":\"etqN3RDAAAd84uMvtvrg9-o2uCoCxDUQ\",\"cityName\":\"江苏省\",\"cityId\":17},{\"tagId\":\"etqN3RDAAAGlkm9v3X-tkuQlVkjZY3ug\",\"cityName\":\"江西省\",\"cityId\":18},{\"tagId\":\"etqN3RDAAA2OJQ-0Y59GTud1y_AGQcDg\",\"cityName\":\"吉林省\",\"cityId\":19},{\"tagId\":\"etqN3RDAAAJ8MLqXbotx-cTqWAPNWuLQ\",\"cityName\":\"辽宁省\",\"cityId\":20},{\"tagId\":\"etqN3RDAAAi0H1aG5vSqVe90EZfoCuOg\",\"cityName\":\"澳门特别行政区\",\"cityId\":21},{\"tagId\":\"etqN3RDAAAV-TUrfSAGcenAVkrxDkx7w\",\"cityName\":\"内蒙古自治区\",\"cityId\":22},{\"tagId\":\"etqN3RDAAAxzeBIbsfxX2rq_5Xbbntig\",\"cityName\":\"宁夏回族自治区\",\"cityId\":23},{\"tagId\":\"etqN3RDAAAlRgGYYegLHXBk0Dza1fZaA\",\"cityName\":\"青海省\",\"cityId\":24},{\"tagId\":\"etqN3RDAAATKtQOfMAoPuAXY_3S6BsoQ\",\"cityName\":\"山东省\",\"cityId\":25},{\"tagId\":\"etqN3RDAAAg6GrDuV2NTLkEmsLcvj8ig\",\"cityName\":\"山西省\",\"cityId\":26},{\"tagId\":\"etqN3RDAAALqtwUQG5i6QDW1sWYMPFFw\",\"cityName\":\"陕西省\",\"cityId\":27},{\"tagId\":\"etqN3RDAAAdHu8MVgFi17nQnetspCg2g\",\"cityName\":\"四川省\",\"cityId\":28},{\"tagId\":\"etqN3RDAAAp_YLCF69OD9GcqqZqIiJsA\",\"cityName\":\"台湾省\",\"cityId\":29},{\"tagId\":\"etqN3RDAAA5wTo1-ShHRKrjeITm2ih8A\",\"cityName\":\"天津市\",\"cityId\":30},{\"tagId\":\"etqN3RDAAA3NY0nQnW-7eaT6ddgJjMJQ\",\"cityName\":\"新疆维吾尔自治区\",\"cityId\":31},{\"tagId\":\"etqN3RDAAANW7bq4Zr16AMSzmKWUMz7g\",\"cityName\":\"西藏自治区\",\"cityId\":32},{\"tagId\":\"etqN3RDAAAyl76opoL0y6HFiBQStYAFw\",\"cityName\":\"云南省\",\"cityId\":33},{\"tagId\":\"etqN3RDAAAI0fV4K_oiIslEspfFZmOfA\",\"cityName\":\"浙江省\",\"cityId\":34},{\"tagId\":\"etqN3RDAAADrQdO_GI89MVN5scv3d0bA\",\"cityName\":\"国外\",\"cityId\":35}]";
//
//        List<CityTag> cityTagList = JSON.parseArray(tagStr, CityTag.class);
//
//        Map<Integer, CityTag> cityTagMap = cityTagList.stream().collect(Collectors.toMap(CityTag::getCityId, a -> a));
//
////        WorkThread thread1 = new WorkThread(dataList, cityTagMap,1);
////        WorkThread thread2 = new WorkThread(dataList, cityTagMap,2);
//        WorkThread thread3 = new WorkThread(dataList, cityTagMap,1);
////        WorkThread thread4 = new WorkThread(dataList, cityTagMap,4);
////        WorkThread thread5 = new WorkThread(dataList, cityTagMap, 0);
//
////        thread1.start();
////        thread2.start();
//        thread3.start();
////        thread4.start();
////        thread5.start();
//
//    }

    static class WorkThread extends Thread{

        private List<Map<String, Object>> dataList;

        private Map<Integer, CityTag> cityTagMap;

        private Integer tag;

        public WorkThread(List<Map<String, Object>> dataList,Map<Integer, CityTag> cityTagMap,Integer tag) {
            this.dataList = dataList;
            this.cityTagMap = cityTagMap;
            this.tag = tag;
        }

        @Override
        public void run() {
            for (int i = 0; i < dataList.size(); i++) {
                if (i % 5 != tag) {
                    continue;
                }

                Map<String, Object> stringObjectMap = dataList.get(i);

                if (Integer.parseInt((String) stringObjectMap.get("id")) < 37328) {
                    continue;
                }


                if (stringObjectMap.get("external_user_id") == null) {
                    continue;
                }

                if (stringObjectMap.get("company_user_id") == null) {
                    continue;
                }

                Integer cityId = Integer.parseInt((String) stringObjectMap.get("parent_id"));
                if (cityId == 0) {
                    cityId = Integer.parseInt((String) stringObjectMap.get("city_id_i"));
                }

                if (cityId == null || cityTagMap.get(cityId) == null) {
                    continue;
                }

                try {
                    String token = "yv0qZHDO6OelCzgiGTnE_xonyK0E1J5b4_zMMUBSHtJTEJC1khGFwc9Bz4ot2VRQ94fSVEF90dWHkjmhwSLc_SOP2ovI1zyjLLX3uoujoIN_wF2LQXkp0M7ixRGAl9kUekGb3l59VMOnJAkGq2g6LnV8i8903vOvFveQT_xY8I2fxKxge4R7O6DVBs5p_Ax0oDriu3A2L_MnqUhQXLeSjg";
                    AddExternalUserMarkDTO addExternalUserMarkDTO = new AddExternalUserMarkDTO();
                    addExternalUserMarkDTO.setUserId((String) stringObjectMap.get("company_user_id"));
                    addExternalUserMarkDTO.setExternalUserid((String) stringObjectMap.get("external_user_id"));
                    addExternalUserMarkDTO.setAddTag(Collections.singletonList(cityTagMap.get(cityId).getTagId()));
                    String params = JSON.toJSONString(addExternalUserMarkDTO);
                    String url = "https://qyapi.weixin.qq.com/cgi-bin/externalcontact/mark_tag?access_token=" + token;
                    String s = doPostBody(url, params);
                    JSONObject jsonObject = JSON.parseObject(s);

                    Integer errcode = jsonObject.getInteger("errcode");

                    if (errcode != 0) {
                        System.out.println("error:" + stringObjectMap.toString());
                        System.out.println(jsonObject);
                        break;
                    }
                    System.out.println("success:" + stringObjectMap.toString());


//                    Thread.sleep(100);

                } catch (Exception e) {
                    System.out.println("exception:" + stringObjectMap.toString());
                    break;
                }
//            break;
            }
        }

    }


    public static String doPostBody(String strURL, String params) {
        try {
            URL url = new URL(strURL);
            HttpURLConnection connection = (HttpURLConnection) url.openConnection();

            connection.setDoOutput(true);
            connection.setDoInput(true);
            connection.setUseCaches(false);
            connection.setInstanceFollowRedirects(true);
            connection.setRequestMethod("POST");
            connection.setRequestProperty("Accept", "application/json");
            connection.setRequestProperty("Content-Type", "application/json");
            connection.connect();

            OutputStreamWriter out = new OutputStreamWriter(connection.getOutputStream(), "UTF-8");
            out.append(params);

            out.flush();
            out.close();
            int length = connection.getContentLength();
            InputStream is = connection.getInputStream();
            if (length != -1) {
                byte[] data = new byte[length];
                byte[] temp = new byte[512];
                int readLen = 0;
                int destPos = 0;
                while ((readLen = is.read(temp)) > 0) {
                    System.arraycopy(temp, 0, data, destPos, readLen);
                    destPos += readLen;
                }
                String result = new String(data, "UTF-8");
                return result;
            }
        } catch (IOException e) {
        }
        return "error";
    }

    public static String httpSendMessage(String nodeUrl, String queryParams) {

        boolean result = false;
        HttpURLConnection urlCon = null;
        StringBuilder tempStr = new StringBuilder();
        try {
            URL url = new URL(nodeUrl + queryParams);
            urlCon = (HttpURLConnection) url.openConnection();
            urlCon.setRequestMethod("GET");
            urlCon.setConnectTimeout(10000);
            urlCon.setReadTimeout(5000);
            urlCon.setDoInput(true);

            InputStream in = urlCon.getInputStream();
            BufferedReader rd = new BufferedReader(new InputStreamReader(in, "UTF-8"));
            String tempLine;
            while ((tempLine = rd.readLine()) != null) {
                tempStr.append(tempLine);
            }
//            logger.info("sendRunwayMsg request: {} ,response: {}", nodeUrl + queryParams, tempStr);
            rd.close();
            in.close();
            result = true;
        } catch (Exception e) {
//            logger.error("消息推送请求异常", e);
        } finally {
            if (urlCon != null) {
                urlCon.disconnect();
            }
        }
        return tempStr.toString();
    }

    /**
     * 读取Excel文件内容
     *
     * @param inputStream 读取Excel文件流
     * @param clazz       返回的实体类泛型
     * @param <T>         泛型
     * @return 文件内容
     */
    public static <T> List<T> read(InputStream inputStream, String fileName, Class<T> clazz) {
        return read(inputStream, fileName, clazz, 0);
    }

    /**
     * 读取Excel文件内容
     *
     * @param inputStream 读取Excel文件流
     * @param clazz       返回的实体类泛型
     * @param sheetNum    sheet下标
     * @param <T>         泛型
     * @return 文件内容
     */
    public static <T> List<T> read(InputStream inputStream, String fileName, Class<T> clazz, int sheetNum) {
        Workbook workbook = readWorkbook(inputStream, fileName);
        assert workbook != null;
        Sheet sheet = workbook.getSheetAt(sheetNum);
        // 判断是否设置“工作铺名称”
        if (clazz.isAnnotationPresent(ExcelSheet.class)) {
            String sheetName = clazz.getAnnotation(ExcelSheet.class).value();
            sheet = workbook.getSheet(sheetName);
        }
        // 获取总行数
        int rows = sheet.getLastRowNum() + 1;
        // 获取列名和列索引关系
        Map<String, Integer> columnMap = getColumnMap(sheet, clazz);

        // 获取类所有属性
        Field[] fields = getAllField(clazz);
        ExcelField excelField;
        // 存储读取到的excel数据
        List<T> list = new ArrayList<>();
        T t;
        Row row;
        Cell cell;
        int columnIndex;
        // 遍历Excel每行
        for (int i = 1; i < rows; i++) {
            row = sheet.getRow(i);
            // 判断空行
            if (isRowEmpty(row)) {
                continue;
            }

            try {
                t = clazz.newInstance();
                for (Field field : fields) {
                    field.setAccessible(true);
                    if (field.isAnnotationPresent(ExcelField.class)) {
                        excelField = field.getAnnotation(ExcelField.class);
                        columnIndex = columnMap.get(excelField.columnName());
                        if (columnIndex != -1) {
                            cell = row.getCell(columnIndex);
                            setFieldValue(t, field, cell);
                        }
                    }
                }
                list.add(t);
            } catch (Exception e) {
//                LOGGER.error("读取Excel文件失败", e);
            }
        }
        try {
            workbook.close();
        } catch (IOException e) {
//            LOGGER.error("关闭workbook失败", e);
        }

        return list;
    }

    /**
     * 读取Excel文件内容
     */
    public static <T> List<T> read(String path, String[] fields, Class<T> clazz, int startRow) {
        List<T> content = new ArrayList<>();
        T t;
        File file = new File(path);
        Workbook workbook = readWorkbook(file);
        assert workbook != null;
        Sheet sheet = workbook.getSheetAt(0);
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        Row row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();
        for (int i = startRow; i <= rowNum; i++) {
            row = sheet.getRow(i);
            // 判断空行
            if (isRowEmpty(row)) {
                continue;
            }

            int j = 0;
            try {
                t = clazz.newInstance();
                while (j < colNum) {
                    //当 j 等于传入的 fields 数组长度时 跳过循环 防止出现数组越界异常
                    if (j == fields.length) {
                        break;
                    }
                    Field declaredField = clazz.getDeclaredField(fields[j]);
                    declaredField.setAccessible(true);
                    setFieldValue(t, declaredField, row.getCell(j));
                    j++;
                }
                content.add(t);
            } catch (InstantiationException | IllegalAccessException | NoSuchFieldException e) {
//                LOGGER.error("读取Excel文件失败", e);
            }
        }
        return content;
    }

    /**
     * 读取Excel文件内容
     *
     * @param path 读取Excel文件的路径
     * @return 文件内容Map集合
     */
    public static List<Map<String, Object>> read(String path) {
        File file = new File(path);
        Workbook workbook = readWorkbook(file);
        assert workbook != null;
        Sheet sheet = workbook.getSheetAt(0);
        // 获取总行数
        int rows = sheet.getLastRowNum() + 1;
        // 获取所有列名

        Row columnNameRow = sheet.getRow(0);
        String[] columnNameArr = new String[columnNameRow.getLastCellNum()];
        for (int i = 0; i < columnNameArr.length; i++) {
            columnNameArr[i] = columnNameRow.getCell(i).getStringCellValue();
        }

        // 存储读取到的excel数据
        List<Map<String, Object>> list = new ArrayList<>();

        Map<String, Object> data;
        Row row;
        // 遍历Excel每行
        for (int i = 1; i < rows; i++) {
            row = sheet.getRow(i);
            // 判断空行
            if (isRowEmpty(row)) {
                continue;
            }

            try {
                data = new HashMap<>();

                for (int j = 0; j < columnNameArr.length; j++) {
                    data.put(columnNameArr[j], getCellValue(row.getCell(j)));
                }
                list.add(data);
            } catch (Exception e) {
//                LOGGER.error("读取Excel文件失败", e);
            }
        }
        try {
            workbook.close();
        } catch (IOException e) {
//            LOGGER.error("关闭workbook失败", e);
        }

        return list;
    }

    /**
     * 创建sheet列名称
     */
    private static <T> void createSheetColumnName(Sheet sheet, CellStyle cellStyle, Class<T> clazz, int startRowNum) {
        Row row = sheet.createRow(startRowNum);
        row.setHeight((short) (20 * 20));
        int columnIndex = 0;
        String columnName;
        ExcelField excelField;
        Field[] declaredFields = clazz.getDeclaredFields();
        for (Field field : declaredFields) {
            if (field != null) {
                // 设置属性可访问
                field.setAccessible(true);
                if (field.isAnnotationPresent(ExcelField.class)) {
                    // 获取注解
                    excelField = field.getAnnotation(ExcelField.class);
                    columnName = excelField.columnName();
                    // 创建单元格
                    createCell(row, columnIndex, columnName, cellStyle);
                    columnIndex++;
                }
            }
        }

    }

    /**
     * 创建sheet数据
     */
    private static <T> void createSheetColumnValue(Sheet sheet, CellStyle cellStyle, List<?> data, Class<T> clazz, Integer startRowNum) {
        int tempStartRowNum = startRowNum;
        Row row;
        int columnIndex;
        Field[] declaredFields = clazz.getDeclaredFields();
        for (Object obj : data) {
            // 创建新行，索引加1,为创建下一行做准备
            row = sheet.createRow(startRowNum++);
            row.setHeight((short) (20 * 20));
            columnIndex = 0;
            for (Field field : declaredFields) {
                if (field != null) {
                    // 设置属性可访问
                    field.setAccessible(true);
                    if (field.isAnnotationPresent(ExcelField.class)) {
                        try {
                            Object value = typeConvert(field.get(obj), field);
                            // 创建单元格  field.get(obj)从obj对象中获取值设置到单元格中
                            createCell(row, columnIndex, value, cellStyle);
                        } catch (IllegalAccessException e) {
//                            LOGGER.error("对象非法访问异常", e);
                        }
                        columnIndex++;
                    }
                }
            }
        }

        ExcelField excelField;
        Field field;
        for (int i = 0; i < declaredFields.length; i++) {
            field = declaredFields[i];
            if (field != null) {
                // 设置属性可访问
                field.setAccessible(true);
                if (field.isAnnotationPresent(ExcelField.class)) {
                    // 获取注解
                    excelField = field.getAnnotation(ExcelField.class);
                    if (excelField.mergeColumnCell()) {
                        mergeColCell(sheet, i, tempStartRowNum, startRowNum - 1);
                    }
                }
            }
        }

    }

    /**
     * 生成Workbook
     */
    private static Workbook buildWorkbook(String fileName, int total) {
        Workbook workbook = null;
        if (fileName.endsWith(EXCEL_XLS)) { // Excel 2003
            workbook = new HSSFWorkbook();
        } else if (fileName.endsWith(EXCEL_XLSX)) { // Excel 2007/2010
            if (total < 1000) {
                workbook = new XSSFWorkbook();
            } else {
                workbook = new SXSSFWorkbook();
            }
        }
        return workbook;
    }

    /**
     * 读取文件的Workbook
     */
    private static Workbook readWorkbook(File file) {
        Workbook workbook = null;
        try (FileInputStream inputStream = new FileInputStream(file)) {
            checkExcelValid(file);
            if (file.getName().endsWith(EXCEL_XLS)) { // Excel 2003
                workbook = new HSSFWorkbook(inputStream);
            } else if (file.getName().endsWith(EXCEL_XLSX)) { // Excel 2007/2010
                workbook = new XSSFWorkbook(inputStream);
            }
        } catch (Exception e) {
//            LOGGER.error("读取Workbook异常", e);
        }
        return workbook;
    }

    /**
     * 读取文件的Workbook
     */
    private static Workbook readWorkbook(InputStream inputStream, String fileName) {
        Workbook workbook = null;
        try {
            if (fileName.endsWith(EXCEL_XLS)) { // Excel 2003
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileName.endsWith(EXCEL_XLSX)) { // Excel 2007/2010
                workbook = new XSSFWorkbook(inputStream);
            }
        } catch (Exception e) {
//            LOGGER.error("读取Workbook异常", e);
        }
        return workbook;
    }

    /**
     * 获取列名和列索引关系
     */
    private static <T> Map<String, Integer> getColumnMap(Sheet sheet, Class<T> clazz) {
        // 获取类所有属性
        Field[] fields = getAllField(clazz);
        ExcelField excelField;
        // 获取列名和列索引关系
        Map<String, Integer> columnMap = new HashMap<>();
        for (Field field : fields) {
            field.setAccessible(true);
            if (field.isAnnotationPresent(ExcelField.class)) {
                excelField = field.getAnnotation(ExcelField.class);
                columnMap.put(excelField.columnName(), getColumnIndex(sheet, excelField.columnName()));
            }
        }

        return columnMap;
    }

    /**
     * 创建Excel单元格
     */
    private static void createCell(Row row, int c, Object cellValue, CellStyle style) {
        Cell cell = row.createCell(c);
        cell.setCellStyle(style);
        //        cell.setCellType(Cell.CELL_TYPE_STRING);
        if (cellValue != null) {
            cell.setCellValue(String.valueOf(cellValue));
        } else {
            cell.setCellValue("");
        }
    }

    /**
     * 获取Excel单元格内容
     */
    private static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        // 把数字当成String来读，避免出现1读成1.0的情况
    /*if(cell.getCellType() == CellType.NUMERIC){
        cell.setCellType(CellType.STRING);
    }*/
        // 判断数据的类型
        switch (cell.getCellType()) {
            case NUMERIC: // 数字
                SimpleDateFormat sdf = null;
                if (HSSFDateUtil.isCellDateFormatted(cell)) { // 处理日期格式、时间格式
                    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else { // 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    }
                    Date date = cell.getDateCellValue();
                    cellValue = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 58) {
                    // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                    sdf = new SimpleDateFormat("yyyy-MM-dd");
                    double value = cell.getNumericCellValue();
                    Date date = DateUtil.getJavaDate(value);
                    cellValue = sdf.format(date);
                } else {
                    cell.setCellType(CellType.STRING);
                    cellValue = String.valueOf(cell.getStringCellValue());
                }
                break;
            case STRING: // 字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: // Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: // 公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK: // 空值
                cellValue = "";
                break;
            case ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    /**
     * 设置对象属性值
     *
     * @param obj   操作对象
     * @param field 对象属性
     * @param cell  excel单元格
     */
    private static void setFieldValue(Object obj, Field field, Cell cell) {
        try {
            if (cell != null) {
                String cellValue = getCellValue(cell);
                if (!(cellValue == null || cellValue.length() == 0)) {
                    if (field.getType() == int.class || field.getType() == Integer.class) {
                        field.set(obj, Integer.valueOf(cellValue));
                    } else if (field.getType() == Double.class) {
                        field.set(obj, Double.parseDouble(cellValue));
                    } else if (field.getType() == LocalDate.class) {
                        field.set(obj, LocalDate.parse(cellValue, DateTimeFormatter.ofPattern("yyyy-MM-dd")));
                    } else {
                        field.set(obj, cellValue);
                    }
                }
            } else {
                if (field.getType() == Number.class) {
                    field.setInt(obj, 0);
                } else {
                    field.set(obj, null);
                }
            }
        } catch (Exception e) {
//            LOGGER.error("设置对象属性值", e);
        }
    }

    /**
     * 创建文件
     *
     * @param path 文件路径
     */
    private static void createFile(String path) {
        if (!Files.exists(Paths.get(path))) {
            try {
                Files.createDirectories(Paths.get(path).getParent());
                Files.createFile(Paths.get(path));
            } catch (IOException e) {
//                LOGGER.error("创建文件失败：{}", path, e);
            }
        }
    }

    /**
     * 校验Excel文件
     */
    private static void checkExcelValid(File file) {
        if (file == null || !Files.exists(file.toPath())) {
            throw new RuntimeException("文件不存在");
        }
        if (!((file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))) {
            throw new RuntimeException("不是Excel文件");
        }
    }

    /**
     * 根据列名（第一行）获取列索引
     *
     * @param sheet      工作铺
     * @param columnName 列名
     * @return 列索引
     */
    private static int getColumnIndex(Sheet sheet, String columnName) {
        int coefficient = -1;
        Row row = sheet.getRow(0);
        int cellNum = row.getPhysicalNumberOfCells();
        for (int i = 0; i < cellNum; i++) {
            if ((row.getCell(i).toString()).equals(columnName)) {
                coefficient = i;
            }
        }
        return coefficient;
    }

    /**
     * 检验是否空行
     */
    private static boolean isRowEmpty(Row row) {
        if (row == null) {
            return true;
        }
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != CellType.BLANK) {
                return false;
            }
        }
        return true;
    }

    /**
     * 数据转换
     */
    private static Object typeConvert(Object value, Field field) {
        if (value != null) {
            JsonFormat jsonFormat;
            String pattern = "yyyy-MM-dd";
            if (field.getType() == Date.class || field.getType() == LocalDateTime.class || field.getType() == LocalDate.class) {
                if (field.isAnnotationPresent(JsonFormat.class)) {
                    jsonFormat = field.getAnnotation(JsonFormat.class);
                    pattern = jsonFormat.pattern();
                }
                if (field.getType() == Date.class) {
                    value = DateUtils.format((Date) value, pattern);
                } else if (field.getType() == LocalDate.class) {
                    DateTimeFormatter formatter = DateTimeFormatter.ofPattern(pattern);
                    value = formatter.format((LocalDate) value);
                } else if (field.getType() == LocalDateTime.class) {
                    DateTimeFormatter formatter = DateTimeFormatter.ofPattern(pattern);
                    value = formatter.format((LocalDateTime) value);
                }
            }
        }

        return value;
    }

    /**
     * 合并单元格
     */
    private static void mergeColCell(Sheet sheet, int colNum, int startRow, int endRow) {
        sheet.addMergedRegion(new CellRangeAddress(startRow, endRow, colNum, colNum));
    }

    /**
     * 写入sheet
     */
    private static <T> void writeSheet(Sheet sheet, Workbook workbook, List<?> data, Class<T> clazz, Integer startRowNum) {
        sheet.setDefaultRowHeightInPoints(20);
        // 设置列名样式
        CellStyle headerStyle = buildHeaderStyle(workbook);
        createSheetColumnName(sheet, headerStyle, clazz, startRowNum);
        startRowNum++;
        // 内容样式
        CellStyle contentStyle = buildContentStyle(workbook);
        createSheetColumnValue(sheet, contentStyle, data, clazz, startRowNum);
    }

    /**
     * 生成列名样式
     */
    private static CellStyle buildHeaderStyle(Workbook workbook) {
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillForegroundColor(IndexedColors.TEAL.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setFontName("黑体");
        font.setColor(IndexedColors.WHITE.getIndex());
        font.setFontHeightInPoints((short) 12);
        headerStyle.setFont(font);
        return headerStyle;
    }

    /**
     * 生成内容样式
     */
    private static CellStyle buildContentStyle(Workbook workbook) {
        CellStyle contentStyle = workbook.createCellStyle();
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return contentStyle;
    }

    /**
     * 判断是否存在某一字段，存在则返回，否则返回null
     */
    private static Field existsField(Class<?> clazz, String fieldName) {
        Field[] fields = clazz.getDeclaredFields();

        clazz = clazz.getSuperclass();
        while (clazz != null) {
            fields = ArrayUtils.addAll(fields, clazz.getDeclaredFields());
            clazz = clazz.getSuperclass();
        }

        for (Field field : fields) {
            if (fieldName.equals(field.getName())) {
                return field;
            }
        }
        return null;
    }

    /**
     * 获得所有字段，包括父类的所有字段
     */
    private static Field[] getAllField(Class<?> clazz) {
        Field[] fields = clazz.getDeclaredFields();

        clazz = clazz.getSuperclass();
        while (clazz != null) {
            fields = ArrayUtils.addAll(fields, clazz.getDeclaredFields());
            clazz = clazz.getSuperclass();
        }

        return fields;
    }

}
